﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using JXT.PrimaryKey.Batman.Extensions;
using JXT.PrimaryKey.Batman.Domain.Quotation.Models;
using JXT.PrimaryKey.Batman.Domain.Quotation.Repositories;
using JXT.PrimaryKey.Batman.Domain.Repositories.MySql;

namespace JXT.PrimaryKey.Batman.Domain.Repositories.Quotation
{
    public class MySqlSecuritiesRepository : MySqlRepositoryBase<Securities, int>, ISecuritiesRepository
    {
        public MySqlSecuritiesRepository(RepositoryContext context)
            : base((MySqlRepositoryContext)context)
        {
        }

        public override void Add(Securities entity)
        {
            string sql = "select count(*) from `securities` where `code`=@code and `markettype`=@markettype";
            var parameterArray = new[]{
                Context.CreateParameter("@code", entity.Code),
                Context.CreateParameter("@markettype", entity.MarketType)
            };
            int count = Convert.ToInt32(Context.ExecuteScalar(sql, parameterArray));
            if (count > 0)
            {
                sql = @"update `securities` set 
                            `stocktype`=@stocktype, 
                            `securitiestype`=@securitiestype, 
                            `name`=@name, 
                            `shortname`=@shortname, 
                            `spell`=@spell,
                            `currencytype`=@currencytype,
                            `industry`=@industry,
                            `circulatingshares`=@circulatingshares
                        where 
                            `code`=@code and `markettype`=@markettype";
                var updateParameterArray = new[]{
                    Context.CreateParameter("@stocktype", entity.StockType),
                    Context.CreateParameter("@securitiestype", entity.SecuritiesType),
                    Context.CreateParameter("@name", entity.Name),
                    Context.CreateParameter("@shortname", entity.ShortName),
                    Context.CreateParameter("@spell", entity.Spell),
                    Context.CreateParameter("@currencytype", entity.CurrencyType),
                    Context.CreateParameter("@industry", entity.Industry),
                    Context.CreateParameter("@circulatingshares", entity.CirculatingShares),
                    Context.CreateParameter("@code", entity.Code),
                    Context.CreateParameter("@markettype", entity.MarketType)
                };
                Context.ExecuteNonQuery(sql, updateParameterArray);
            }
            else
            {
                base.Add(entity);
            }
        }

        public IEnumerable<Securities> SearchList(string condition, int num, MarketType marketType = MarketType.Unknown, SecuritiesType securitiesType = SecuritiesType.Unknown)
        {
            bool isSpell = false;
            bool isKeyworks = false;
            bool isNember = false;
            IList<Securities> newList = new List<Securities>();
            string oldCondition = condition.Clone().ToString();
            string sql = "";
            if (!condition.IsNumber())
            {
                    sql = "select * from `securities` where (`code` like @code or `name` like @name or `spell` like @spell)";
            }
            else 
            {
                 sql = "select * from `securities` where (`code` like @code)";
            }
           
            if (marketType.HasFlag(MarketType.Hongkong) || marketType.HasFlag(MarketType.Shanghai)
                || marketType.HasFlag(MarketType.Shenzhen))
            {
                sql += " and (";
                if (marketType.HasFlag(MarketType.Shanghai))
                {
                    if (!sql.EndsWith("("))
                        sql += " or ";
                    sql += "`markettype`=" + (int)MarketType.Shanghai;
                }
                if (marketType.HasFlag(MarketType.Hongkong))
                {
                    if (!sql.EndsWith("("))
                        sql += " or ";
                    sql += "`markettype`=" + (int)MarketType.Hongkong;
                }
                if (marketType.HasFlag(MarketType.Shenzhen))
                {
                    if (!sql.EndsWith("("))
                        sql += " or ";
                    sql += "`markettype`=" + (int)MarketType.Shenzhen;
                }
                sql += ")";
            }
            if (securitiesType.HasFlag(SecuritiesType.Stock) || securitiesType.HasFlag(SecuritiesType.Index) || securitiesType.HasFlag(SecuritiesType.Bart) || securitiesType.HasFlag(SecuritiesType.AuthorityCard)
                || securitiesType.HasFlag(SecuritiesType.Fund) || securitiesType.HasFlag(SecuritiesType.NiuXiongCard) || securitiesType.HasFlag(SecuritiesType.NiuXiongCard) || securitiesType.HasFlag(SecuritiesType.National)
                || securitiesType.HasFlag(SecuritiesType.Trust) || securitiesType.HasFlag(SecuritiesType.Venture))
            {
                sql += " and (";
                if (securitiesType.HasFlag(SecuritiesType.Stock))
                {
                    if (!sql.EndsWith("("))
                        sql += " or ";
                    sql += "`securitiesType`=" + (int)SecuritiesType.Stock;
                }
                if (securitiesType.HasFlag(SecuritiesType.Index))
                {
                    if (!sql.EndsWith("("))
                        sql += " or ";
                    sql += "`securitiesType`=" + (int)SecuritiesType.Index;
                }
                if (securitiesType.HasFlag(SecuritiesType.Bart))
                {
                    if (!sql.EndsWith("("))
                        sql += " or ";
                    sql += "`securitiesType`=" + (int)SecuritiesType.Bart;
                }
                if (securitiesType.HasFlag(SecuritiesType.AuthorityCard))
                {
                    if (!sql.EndsWith("("))
                        sql += " or ";
                    sql += "`securitiesType`=" + (int)SecuritiesType.AuthorityCard;
                }
                if (securitiesType.HasFlag(SecuritiesType.Fund))
                {
                    if (!sql.EndsWith("("))
                        sql += " or ";
                    sql += "`securitiesType`=" + (int)SecuritiesType.Fund;
                }
                if (securitiesType.HasFlag(SecuritiesType.NiuXiongCard))
                {
                    if (!sql.EndsWith("("))
                        sql += " or ";
                    sql += "`securitiesType`=" + (int)SecuritiesType.NiuXiongCard;
                }
                if (securitiesType.HasFlag(SecuritiesType.National))
                {
                    if (!sql.EndsWith("("))
                        sql += " or ";
                    sql += "`securitiesType`=" + (int)SecuritiesType.National;
                }
                if (securitiesType.HasFlag(SecuritiesType.Trust))
                {
                    if (!sql.EndsWith("("))
                        sql += " or ";
                    sql += "`securitiesType`=" + (int)SecuritiesType.Trust;
                }
                if (securitiesType.HasFlag(SecuritiesType.Venture))
                {
                    if (!sql.EndsWith("("))
                        sql += " or ";
                    sql += "`securitiesType`=" + (int)SecuritiesType.Venture;
                }
                sql += ")";
            }
            if (condition.IsNumber())
            {
                sql += " order by `code`,`securitiesType`,`markettype`,`name`,`spell` asc";
                if (condition.Substring(0, 1) != "0")
                {
                    condition = String.Format("{0}%", condition);
                }
                else 
                {
                    condition = String.Format("%{0}%", condition);
                }
                isNember = true;
            }
            else if (condition.IsLetter())
            {
                sql += " order by `spell`,`code`,`markettype`,`securitiesType`,`name` asc";
                condition = String.Format("{0}%",condition);
                isSpell = true;
            }
            else
            {
                sql += " order by `markettype`,`securitiesType`,`code`,`name`,`spell` asc";
                condition = String.Format("{0}%",condition);
                isKeyworks = true;
            }
            sql += " limit " + num;
            IDataParameter[] parameterArray ;
            if (oldCondition.IsNumber())
            {
                     parameterArray = new[]{
                    Context.CreateParameter("@code", condition),
                };
            }
            else 
            {
                     parameterArray = new[]{
                    Context.CreateParameter("@code", condition),
                    Context.CreateParameter("@name", condition),
                    Context.CreateParameter("@spell", condition)
                };
            }
            IList<Securities> list = new List<Securities>();
            using (var dr = Context.ExecuteDataReader(sql, parameterArray))
            {
                while (dr.Read())
                {
                    list.Add(ReadEntityFromDataReader(dr));
                }
            }

            //处理数字搜索条件
            if (isNember)
            {
                if (list.Count() < num)
                {
                    condition = string.Format("%{0}", condition);
                    parameterArray = new[]{
                    Context.CreateParameter("@code", condition),
                };
                    using (var dr = Context.ExecuteDataReader(sql, parameterArray))
                    {
                        while (dr.Read())
                        {
                            list.Add(ReadEntityFromDataReader(dr));
                        }
                    }

                }
            }

           //处理拼音搜索条件
            if (isSpell)
            {
                if (list.Count() < num)
                {
                    condition = String.Format("%{0}%", condition);
                    parameterArray = new[]{
                    Context.CreateParameter("@code", condition),
                    Context.CreateParameter("@name", condition),
                    Context.CreateParameter("@spell", condition)
                };
                    using (var dr = Context.ExecuteDataReader(sql, parameterArray))
                    {
                        while (dr.Read())
                        {
                            list.Add(ReadEntityFromDataReader(dr));
                        }
                    }
                }
            }

            //处理关键字搜索条件
            if (isKeyworks)
            {
                if (list.Count() < num)
                {
                    condition = String.Format("%{0}%", condition);
                    parameterArray = new[]{
                    Context.CreateParameter("@code", condition),
                    Context.CreateParameter("@name", condition),
                    Context.CreateParameter("@spell", condition)
                };
                    using (var dr = Context.ExecuteDataReader(sql, parameterArray))
                    {
                        while (dr.Read())
                        {
                            list.Add(ReadEntityFromDataReader(dr));
                        }
                    }
                }
            }

            list = list.Distinct().Take(num).ToList();
            return list;
        }

        public IEnumerable<Securities> GetList(MarketType market = MarketType.Unknown)
        {
            string sql = "select * from `securities`";
            if (market.HasFlag(MarketType.Shanghai))
            {
                if (sql.EndsWith("`securities`"))
                    sql += " where ";
                else
                    sql += " or ";
                sql += "`markettype`=" + (int)MarketType.Shanghai;
            }
            if (market.HasFlag(MarketType.Hongkong))
            {
                if (sql.EndsWith("`securities`"))
                    sql += " where ";
                else
                    sql += " or ";
                sql += "`markettype`=" + (int)MarketType.Hongkong;
            }
            if (market.HasFlag(MarketType.Shenzhen))
            {
                if (sql.EndsWith("`securities`"))
                    sql += " where ";
                else
                    sql += " or ";
                sql += "`markettype`=" + (int)MarketType.Shenzhen;
            }
            IList<Securities> list = new List<Securities>();
            using (var dr = Context.ExecuteDataReader(sql))
            {
                while (dr.Read())
                {
                    list.Add(ReadEntityFromDataReader(dr));
                }
            }
            return list;
        }

        public Securities GetSingle(string code, MarketType marketType = MarketType.Unknown)
        {
            string sql = "select * from `securities` where `code`=@code";
            IList<IDataParameter> parameterList = new List<IDataParameter>();
            parameterList.Add(Context.CreateParameter("code", code));
            if (marketType != MarketType.Unknown)
            {
                sql += " and `markettype`=@markettype";
                parameterList.Add(Context.CreateParameter("markettype", marketType));
            }
            Securities result = null;
            using (var dr = Context.ExecuteDataReader(sql, parameterList.ToArray()))
            {
                if (dr.Read())
                {
                    result = ReadEntityFromDataReader(dr);
                }
            }
            return result;
        }
    }
}
